﻿/// -----------------------------------------------------------------
/// KeywordLoader.cs: Helper class to create Objects from Keyword entries in the Database.
/// License: see http://mve.codeplex.com/license; All Rights follows the MS-PL
/// Current owner: shiniwa
/// The project decription: please refer to http://codeplex.com/mve/
/// -----------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace MVEContentSvc.KeywordOperations
{
    /*
     * 日本SGI株式会社
     * ヨハンソン・ヘンリク
     * johansson@sgi.co.jp
     * 
     * Change Log:
     * ---------------------------------------------------------------------------
     * 2009.01.07	Johansson Henrik		Class Created.
     * 
     */

    /// <summary>
    /// Helper class to create Objects from Keyword entries in the Database.
    /// Use this class to Load Keywords from the Databse.
    /// </summary>
    /// <example>Keyword kw = LoadKeywordFromDatabase(1, sqlConnection);</example>
    /// <seealso cref="Keyword"/>
    /// <remarks>INSERT:Creds</remarks>
    internal static class KeywordLoader
    {
        /// <summary>
        /// Loads a single Keyword from the DB into a Keyword object.
        /// Requires that no other commands are open on the connection used to fetch the Keyword.
        /// </summary>
        /// <param name="keywordId">Id of Keyword to load</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>Oject Representation of the loaded Keyword.</returns>
        internal static Keyword LoadKeywordFromDatabase(int keywordId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            Keyword result = null;

            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetKeywordById";
                command.Parameters.Add("keywordId", SqlDbType.Int).Value = keywordId;
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        result = new KeywordOperations.Keyword()
                        {
                            id = reader.GetInt32(0),
                            ClipId = -1,
                            SegmentId = reader.GetInt32(2),
                            UserId = reader.GetInt32(3),
                            KeywordString = reader.GetString(4)
                        };
                    }
                }
            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:LoadKeywordFromDatabase

        /// <summary>
        /// Loads all the Keywords for a single segment from the DB into a list of Keyword objects.
        /// Requires that no other commands are open on the connection used to fetch the Keywords.
        /// </summary>
        /// <param name="segmentId">Id of segment whose keywords to load</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>List of Oject Representations of the loaded Keywords.</returns>
        internal static List<Keyword> LoadKeywordsForSegmentFromDatabase(int segmentId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            List<Keyword> result = null;

            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetAllKeywordsForSegment";
                command.Parameters.Add("SegmentId", SqlDbType.Int).Value = segmentId;
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    result = new List<Keyword>();

                    while (reader.Read())
                    {
                        KeywordOperations.Keyword kw = new KeywordOperations.Keyword()
                        {
                            id = reader.GetInt32(0),
                            ClipId = -1,
                            SegmentId = reader.GetInt32(2),
                            UserId = reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
                            KeywordString = reader.GetString(4)
                        };

                        result.Add(kw);
                    }
                }
            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:LoadKeywordsForSegmentFromDatabase
    }
}
